﻿DELIMITER $$

DROP PROCEDURE IF EXISTS `cleanPaths`$$

CREATE PROCEDURE `cleanPaths` ()
BEGIN
DECLARE pathID INT;
DECLARE pathName VARCHAR(512);
DECLARE numberFound INT;
DECLARE no_more_rows BOOLEAN;
    
    DECLARE curPaths CURSOR FOR
        SELECT idPath, strPath
        FROM path
        WHERE idPath NOT IN (SELECT idPath FROM song);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
    
    CREATE TEMPORARY TABLE songpaths (idPath integer, strPath varchar(512));
    
    INSERT INTO songpaths
    SELECT idPath, strPath FROM path WHERE idPath IN (SELECT idPath FROM song);
    
    OPEN curPaths;
    
    the_loop: LOOP
        FETCH curPaths INTO pathID, pathName;
        
        IF no_more_rows THEN
            CLOSE curPaths;
            LEAVE the_loop;
        END IF;
        
        SELECT COUNT(idPath) INTO numberFound FROM songpaths WHERE strPath LIKE CONCAT('%', pathName, '%');

        IF (numberFound <= 0) THEN
            SELECT CONCAT('DELETE FROM path WHERE idPath = ', CAST(pathID AS CHAR)), pathName;
        END IF;
    END LOOP the_loop;
    
    DROP TABLE songpaths;
END;
